Getting a Specified Cast is not valid while importing data from Excel using Linq to SQL
- by niceoneishere
This is my second post. After learning from my first post how fantastic is to use Linq to SQL, I wanted to try to import data from a Excel sheet into my SQL database.
First My Excel Sheet:
it contains 4 columns namely
ItemNo
ItemSize
ItemPrice
UnitsSold
I have a created a database table with the following fields
table name ProductsSold
Id int not null identity --with auto increment set to true
ItemNo VarChar(10) not null
ItemSize VarChar(4) not null
ItemPrice Decimal(18,2) not null
UnitsSold int not null
Now I created a dal.dbml file based on my database and I am trying to import the data from excel sheet to db table using the code below.
Everything is happening on click of a button.
private const string forecast_query = "SELECT ItemNo, ItemSize, ItemPrice, UnitsSold FROM [Sheet1$]";
protected void btnUpload_Click(object sender, EventArgs e)
{
var importer = new LinqSqlModelImporter();
if (fileUpload.HasFile)
{
var uploadFile = new UploadFile(fileUpload.FileName);
try
{
fileUpload.SaveAs(uploadFile.SavePath);
if(File.Exists(uploadFile.SavePath))
{
importer.SourceConnectionString = uploadFile.GetOleDbConnectionString();
importer.Import(forecast_query);
gvDisplay.DataBind();
pnDisplay.Visible = true;
}
}
catch (Exception ex)
{
Response.Write(ex.Source.ToString());
lblInfo.Text = ex.Message;
}
finally
{
uploadFile.DeleteFileNoException();
}
}
}
// Now here is the code for LinqSqlModelImporter
public class LinqSqlModelImporter : SqlImporter
{
public override void Import(string query)
{
// importing data using oledb command and inserting into db using LINQ to SQL
using (var context = new WSDALDataContext())
{
using (var myConnection = new OleDbConnection(base.SourceConnectionString))
using (var myCommand = new OleDbCommand(query, myConnection))
{
myConnection.Open();
var myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
context.ProductsSolds.InsertOnSubmit(new ProductsSold()
{
ItemNo = myReader.GetString(0),
ItemSize = myReader.GetString(1),
ItemPrice = myReader.GetDecimal(2),
UnitsSold = myReader.GetInt32(3)
});
}
}
context.SubmitChanges();
}
}
}
can someone please tell me where am I making the error or if I am missing something, but this is driving me nuts.
When I debugged I am getting this error
when casting from a number the value must be a number less than infinity
I really appreciate it
